package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.DataTemplateDaoImpl;
import com.data.entity.BaseData;
@Repository("baseDataDao")
public class BaseDataDaoImpl extends DataTemplateDaoImpl<BaseData, Long>implements BaseDataDao {
	/**
	 * 获得离查询时间点最近的数据记录(避免了漏数据引起的错误)
	 */
	@Override
	public BaseData getBaseDataByEquNoAndDate(String equNo, String date) {
//		String sql="select top 1 *,abs(DATEDIFF(SS,CONVERT(VARCHAR(19),Time,120),:date)) as ts from baseData where equNo=:equNo   order by ts";
		String sql="select top 1 P_engry,time from baseData where equNo=:equNo and  CONVERT(VARCHAR(19),time,120)<=:date  order by time desc ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("date", date);
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
	/**
	 * 查询第一条记录
	 * @return
	 */
	@Override
	public BaseData getFirstBaseData() {
	     String sql=" select top 1 * from baseData order by id";
	     Map<String, Object> params=new HashMap<String, Object>();
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
	/**
	 * 查询时间段内的实时数据记录
	 * @param equNo
	 * @param startTime
	 * @param endTime
	 * @return
	 */
	@Override
	public List<BaseData> getBaseDataByAearDate(String equNo, String startTime,String endTime) {
		String sql="select * from baseData where equNo=:equNo and P_engry is not null and CONVERT(VARCHAR(19),time,120)>=:startTime and  CONVERT(VARCHAR(19),time,120)<:endTime";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
	/**
	 * 查询时间段内设备的不同状态记录
	 * @param equNo
	 * @param startTime
	 * @param endTime
	 * @param lowValue
	 * @param highValue
	 * @return
	 */
	@Override
	public int getCountBaseDatasByDateAndPower(String equNo, String startTime,
			String endTime, double lowValue, double highValue) {
		String sql="select COUNT(*) from baseData where  equNo=:equNo and P_engry is not null and CONVERT(VARCHAR(19),time,120)>=:startTime  and CONVERT(VARCHAR(19),time,120)<:endTime  and P_active>=:lowValue  and P_active<:highValue ";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		params.put("lowValue", lowValue);
		params.put("highValue", highValue);
		return jt.queryForObject( sql.toString(), params,Integer.class);
	}
	/**
	 * 获取最后一条设备实时数据
	 */
	@Override
	public BaseData getLastBaseData(String equNo) {
		String sql="select top 1 * from baseData where equNo=:equNo and P_engry is not null order by time desc ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		return jt.queryForObject(sql, params, 
				ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
    /**
     * 查询某一时间点之后的第一条数据
     * @param equNo
     * @param date
     * @return
     */
	@Override
	public BaseData getBaseDataByDate(String equNo, String date) {
		String sql="select top 1 * from (select  * from baseData where equNo=:equNo and P_engry is not null  and  CONVERT(VARCHAR(19),time,120)>:date) as bd";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("date", date);
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
    /**
     * 查询每日整点电度数据
     * @param equNo
     * @param date
     * @return
     */
	@Override
	public List<BaseData> getRealTimePowers(String equNo, String date) {
		String sql="select P_active,Time from baseData where Convert(varchar(10),Time,120)=:date and equNo=:equNo and  SUBSTRING(CONVERT(VARCHAR(8),Time,108),4,5)='00:00' order by Time asc";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("date", date);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
		}
    /**
     * 查询某天的最大功率值
     * @param equNo
     * @param date
     * @return
     */
	@Override
	public Double getMaxP_active(String equNo, String date) {
		String sql="select MAX(P_active) from baseData where  Convert(varchar(10),Time,120)=:date and equNo=:equNo";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("date", date);
		return jt.queryForObject(sql.toString(), params, Double.class);
		}
    /**
     * 查询时间区间内的最大功率值
     * @param equNo
     * @param startDate
     * @param endDate
     * @return
     */
	@Override
	public Double getMaxP_activeByTimes(String equNo, String startDate,
			String endDate) {
		String sql="select MAX(P_active) from baseData where equNo=:equNo and Convert(varchar(10),Time,120)>=:startDate and Convert(varchar(10),Time,120)<=:endDate ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql.toString(), params, Double.class);
		}
    /**
     * 不区分设备查询时间段实时数据
     * @param startTime
     * @param endTime
     * @return
     */
	@Override
	public List<BaseData> getBaseDataByTime(String startTime, String endTime) {
		String sql="select * from baseData where P_engry is not null and CONVERT(VARCHAR(19),time,120)>=:startTime and  CONVERT(VARCHAR(19),time,120)<:endTime";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
    /**
     * 查询某一功率下的某一时间段的记录
     * @param equNo
     * @param startTime
     * @param endTime
     * @param highValue
     * @param lowValue
     * @return
     */
	@Override
	public List<BaseData> getBaseDataByPower(String equNo,String startTime,String endTime,double highValue,double lowValue) {
		String sql="select P_engry,Time  from baseData where equNo=:equNo and CONVERT(VARCHAR(19),time,120)>=:startTime and  CONVERT(VARCHAR(19),time,120)<:endTime and   P_active>=:lowValue and P_active<:highValue order by Time";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equNo", equNo);
		params.put("startTime", startTime);
		params.put("endTime", endTime);
		params.put("highValue", highValue);
		params.put("lowValue", lowValue);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(BaseData.class));
	}
}
